/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sistema.entidades;

import br.com.sistema.excecoes.EntidadeNaoEncontrada;
import br.com.sistema.infraestrutura.ConnectionPool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Lauro
 */
public class ItemMenu {

    private int id;
    private String nome;
    private int indice;
    private Menu menu;
    private Pagina pagina;
    private Connection conexao;

    public ItemMenu(String nome, Menu menu) {
        this.nome = nome;
        this.menu = menu;
    }

    public ItemMenu(int id) throws SQLException, EntidadeNaoEncontrada {
        this.id = id;
        buscar();
    }

    public ItemMenu(int id, String nome, int indice) {
        this.id = id;
        this.nome = nome;
        this.indice = indice;
    }

    public ItemMenu(int id, String nome, int indice, Menu menu) {
        this.id = id;
        this.nome = nome;
        this.indice = indice;
        this.menu = menu;
    }

    public static ItemMenu getItemMenu(int id) throws SQLException {
        try {
            return new ItemMenu(id);
        } catch (EntidadeNaoEncontrada ex) {
            return null;
        }
    }

    //<editor-fold defaultstate="collapsed" desc="Metodos Getters & Setters.">
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public int getIndice() {
        return indice;
    }

    public void setIndice(int indice) {
        this.indice = indice;
    }

    public Pagina getPagina() throws SQLException {
        if (pagina == null) {
            pagina = Pagina.getPagina(this);
        }
        return pagina;
    }

    public void setPagina(Pagina pagina) {
        this.pagina = pagina;
    }

    public Menu getMenu() {
        return menu;
    }

    public void setMenu(Menu menu) {
        this.menu = menu;
    }

    public Connection getConexao() {
        if (conexao == null) {
            this.conexao = ConnectionPool.getConnection();
        }
        return conexao;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="Strings SQL.">
    private String getSQLInsercao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO item_menu ( ");
        sql.append("   nome, ");
        sql.append("   indice, ");
        sql.append("   menu ");
        sql.append(" ) VALUES ( ");
        sql.append("   ?, ");
        sql.append("   ?, ");
        sql.append("   ? ");
        sql.append(" ) ");
        return sql.toString();
    }

    private String getSQLAtualizacao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE ");
        sql.append(" item_menu ");
        sql.append(" SET ");
        sql.append(" nome=?, ");
        sql.append(" indice=?, ");
        sql.append(" menu=? ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }

    private String getSQLExclusao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ");
        sql.append(" item_menu ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }

    protected String getSQLBuscarPorId() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * ");
        sql.append(" FROM item_menu ");
        sql.append(" WHERE id = ? ");
        sql.append(" ORDER BY indice ");
        return sql.toString();
    }

    private static String getSQLBuscarPorMenu() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * ");
        sql.append(" FROM item_menu ");
        sql.append(" WHERE menu = ? ");
        sql.append(" ORDER BY indice ");
        return sql.toString();
    }

    protected String getSQLNextIndice() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT MAX(item_menu.indice) AS next ");
        sql.append(" FROM item_menu ");
        sql.append(" WHERE menu = ? ");
        return sql.toString();
    }
    // </editor-fold>

    // BEGIN: CRUD
    //<editor-fold defaultstate="expanded" desc="Metodos CRUD.">
    public void buscar() throws SQLException, EntidadeNaoEncontrada {
        //buscar pelo casamento
        getConexao();
        PreparedStatement query = getConexao().prepareStatement(getSQLBuscarPorId());
        query.setInt(1, this.getId()); // id
        ResultSet result = query.executeQuery();
        if (result.next()) {
            this.setId(result.getInt("id"));
            this.setNome(result.getString("nome"));
            this.setIndice(result.getInt("indice"));
        } else {
            throw new EntidadeNaoEncontrada();
        }
    }

    public static List<ItemMenu> getItemMenu(Menu menu) throws SQLException {
        List<ItemMenu> itensMenu = new ArrayList<ItemMenu>();
        Connection conexao = ConnectionPool.getConnection();
        PreparedStatement query = conexao.prepareStatement(getSQLBuscarPorMenu());
        query.setInt(1, menu.getId()); // menu ID
        ResultSet result = query.executeQuery();
        while (result.next()) {
            ItemMenu item = new ItemMenu(result.getInt("id"),
                    result.getString("nome"),
                    result.getInt("indice"),
                    menu);
            itensMenu.add(item);
        }
        return itensMenu;
    }

    public void salvar() throws SQLException {
        getConexao();
        conexao.setAutoCommit(false);
        inserir();
        conexao.commit();
    }

    private void inserir() throws SQLException {
        this.setIndice(getNextIndice());
        PreparedStatement query = getConexao().prepareStatement(getSQLInsercao(), Statement.RETURN_GENERATED_KEYS);
        query.setString(1, this.getNome()); // nome
        query.setInt(2, this.getIndice()); // indice
        query.setInt(3, this.getMenu().getId()); // menu ID
        query.executeUpdate();
        ResultSet result = query.getGeneratedKeys();
        result.first();
        int result_id = result.getInt(1);
        this.setId(result_id);
    }

    public void alterar() throws SQLException {
        getConexao();
        conexao.setAutoCommit(false);
        atualizar();
        conexao.commit();
    }

    private void atualizar() throws SQLException {
        PreparedStatement query = getConexao().prepareStatement(getSQLAtualizacao());
        query.setString(1, this.getNome()); // nome
        query.setInt(2, this.getIndice()); // indice
        query.setInt(3, this.getMenu().getId()); // menu ID
        query.setInt(4, this.getId()); // id
        query.executeUpdate();
    }

    public void remover() throws SQLException {
        getConexao();
        conexao.setAutoCommit(false);
        excluir();
        conexao.commit();
    }

    private void excluir() throws SQLException {
        PreparedStatement query = getConexao().prepareStatement(getSQLExclusao());
        query.setInt(1, this.getId()); // id
        query.executeUpdate();
    }
    // </editor-fold>
    // END: CRUD

    private int getNextIndice() throws SQLException {
        PreparedStatement query = getConexao().prepareStatement(getSQLNextIndice());
        query.setInt(1, this.getMenu().getId());
        ResultSet result = query.executeQuery();
        if (result.next()) {
            return result.getInt("next") + 1;
        } else {
            return 1;
        }
    }
}
